package com.example.demo.mapper;

import com.example.demo.entity.Role;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    /**
     * 注册
     * */
    @Update("UPDATE USER SET user_name=#{user_name},user_password=MD5(#{user_password}),user_status=1 WHERE user_email=#{user_email} and user_code=MD5(#{user_code})")
    public int reg(String user_name, String user_password, String user_email,String user_code);
    @Select("select user_id from user where user_email=#{user_email}")//查询邮箱是否被注册
    public User userid(String user_email);
//    @Delete("delete from user where user_email=#{user_email}")//若邮箱被注册，则删除
//    public int deluser(String user_email);
    @Insert("insert into user_role values(default,#{user_id},1)")
    public int role(Integer user_id);//选定用户为学生角色

    @Select("select user_id from user where user_email=#{user_email}")
    public User select_email(String user_email);//查询邮箱是否被注册
    //发送邮件
    @Insert("insert into user values(default,null,null,#{user_email},null,MD5(#{user_code}))")
    public int emiles(String user_email,String user_code);
    /**
     * 登录
     * */
    @Select("select * from user where user_email=#{user_email} and user_password=MD5(#{user_password})")
    public User log(String user_email,String user_password);
    /**
     * 查看个人信息
     * */
    @Select("select * from user where user_email=#{user_email}")
    public User toview(String user_email);
    /**
     * 查询所有学生
     * */
    @Select("SELECT user.user_id,user.user_name,user.user_email,role.role_name,user.user_status FROM USER LEFT JOIN user_role ON user.user_id=user_role.user_id LEFT JOIN ROLE ON user_role.role_id=role.role_id limit #{page},#{limit}")
    public List<HashMap> selstu(Integer page,Integer limit);
    @Select("SELECT COUNT(*) FROM user")
    public int selusernums();
    /**
     * 更改用户状态
     * */
    @Update("update user set user_status=#{user_status} where user_email=#{user_email}")
    public int update(Integer user_status,String user_email);
    /**
     * 用户自行更改密码
     * */
    @Update("update user set user_password=MD5(#{new_user_password}) where user_email=#{user_email} and user_password=MD5(#{user_password})")
    public int uppwd(String new_user_password,String user_password,String user_email);
    /**
     * 管理员强制重置用户密码
     * */
    @Update("update user set user_password=MD5('123456') where user_email=#{user_email}")
    public int admuppwd(String user_email);
    /**
     * 查询角色
     * */
    @Select("select * from role")
    public List<Role> selrole();
    /**
     * 查询用户角色
     * */
    @Select("SELECT role.* FROM USER LEFT JOIN user_role AS u ON user.`user_id`=u.`user_id` LEFT JOIN role ON role.`role_id`=u.`role_id` WHERE user.`user_email`=#{user_email}")
    public Role seluserrole(String user_email);

    /**
     * 模糊查询用户
     */
    @Select("<script>" +
            "SELECT role.role_name,user.* FROM user LEFT JOIN user_role ON user.user_id=user_role.user_id LEFT JOIN ROLE ON user_role.role_id=role.role_id WHERE 1=1"+
            "<if test='user_name!=null'> and user_name LIKE concat('%',#{param1},'%')</if>"+
            "<if test='user_email!=null'> and user_email LIKE concat('%',#{param2},'%')</if>"+
            "</script>")
    public List<HashMap> selusermohu( String user_name, String user_email);

}
